﻿/***********************************************************************************************************************************
 *    Name: Configure_RecycleTime.sql
 *  Author: Frank Figearo — http://www.sqlnerd.me/ — frank@sqlnerd.me
 * Summary:	
 */
SET IMPLICIT_TRANSACTIONS OFF; IF 0 < @@TRANCOUNT ROLLBACK;
USE msdb;
GO
CREATE CREDENTIAL [Recycler] WITH IDENTITY= N'BBAAviation\SSRSPOSService', SECRET= N'$q!P0$**';
GO
EXECUTE msdb.dbo.sp_add_proxy
	@proxy_name		= N'Recycler',
	@credential_name= N'Recycler',
	@enabled		= 1;
EXECUTE msdb.dbo.sp_grant_proxy_to_subsystem
	@proxy_name		= N'Recycler',
	@subsystem_id	= 12;	-- PowerShell
GO

BEGIN TRANSACTION;
DECLARE
  @JobID		BINARY(16),
  @ReturnCode	INT			= 0;

EXEC @ReturnCode= msdb.dbo.sp_add_job
	@job_name				= N'Configure SSRS Recycle', 
	@enabled				= 1, 
	@notify_level_eventlog	= 0, 
	@notify_level_email		= 0, 
	@notify_level_netsend	= 0, 
	@notify_level_page		= 0, 
	@delete_level			= 0, 
	@owner_login_name		= N'sa',
	@job_id					= @JobID OUTPUT;
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback;

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep
	@job_id					= @JobID,
	@step_name				= N'Grant File Permission', 
	@step_id				= 1, 
	@cmdexec_success_code	= 0, 
	@on_success_action		= 3, 
	@on_success_step_id		= 0, 
	@on_fail_action			= 2, 
	@on_fail_step_id		= 0, 
	@retry_attempts			= 0, 
	@retry_interval			= 0, 
	@os_run_priority		= 0,
	@subsystem				= N'CmdExec', 
	@command				= N'ICACLS "C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer\rsreportserver.config" /GRANT BBAAviation\SSRSPOSService:M', 
	@flags=32;
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback;

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep
	@job_id					= @JobID,
	@step_name				= N'Modify RSReportServer.config', 
	@step_id				= 2, 
	@cmdexec_success_code	= 0, 
	@on_success_action		= 1, 
	@on_success_step_id		= 0, 
	@on_fail_action			= 2, 
	@on_fail_step_id		= 0, 
	@retry_attempts			= 0, 
	@retry_interval			= 0, 
	@os_run_priority		= 0,
	@subsystem				= N'PowerShell', 
	@command				= N'Set-Location "C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer";
$file= Get-Content "rsreportserver.config";
$file | Foreach-Object {$_ -replace "<RecycleTime>720</RecycleTime>", "<RecycleTime>1500</RecycleTime>"} | Set-Content "rsreportserver.config";
Write-Output COMPLETE;', 
	@database_name			= N'master', 
	@flags					= 32, 
	@proxy_name				= N'Recycler';
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback;

EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id= @JobID, @start_step_id= 1;
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback;

EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id= @JobID, @server_name= N'(local)';
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback;

COMMIT TRANSACTION; GOTO EndSave;
QuitWithRollback:; IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION;
EndSave:;
GO

EXECUTE sp_start_job @job_name= N'Configure SSRS Recycle';
WAITFOR DELAY '0:0:06';
SELECT h.[message] FROM sysjobs j INNER JOIN sysjobhistory h ON (j.job_id = h.job_id) WHERE j.[name] =  N'Configure SSRS Recycle';
GO


	EXECUTE msdb.dbo.sp_delete_job @job_name= N'Configure SSRS Recycle';
	EXECUTE msdb.dbo.sp_delete_proxy @proxy_name= N'Recycler';
	DROP CREDENTIAL [Recycler];
GO